package com.ithailong.reconsitutionmassion.util;

import java.beans.PropertyDescriptor;
import java.lang.reflect.Constructor;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * author:hlc
 * date:2023/10/15
 *
 * @author 17930
 */

/**
 * 创建数据库链接
 */
public class Dbutils {
   private String url = "jdbc:mysql://8.134.144.39:3306/massion?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false";
   private String user = "root";
   private String password = "9331de57fbd27ed3";
   private Connection connection;
   private PreparedStatement statement;
   private ResultSet resultSet;
   private boolean autoCommit = true;

   static {
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
      } catch (ClassNotFoundException e) {
         throw new RuntimeException(e);
      }
   }

   public Connection getConnection() throws SQLException {
      if (connection == null || connection.isClosed()) {
         try {
            connection = DriverManager.getConnection(url, user, password);
         } catch (SQLException e) {
            throw new RuntimeException(e);
         }
      }
      return connection;
   }

   public void startTransaction() throws SQLException {
      getConnection();
      if (connection != null && !connection.isClosed() && autoCommit == false) {
         connection.commit();
         autoCommit = true;
      }
   }

   public void commit() throws SQLException {
      if (connection != null && !connection.isClosed() && autoCommit == false) {
         connection.commit();
         autoCommit = true;
      }
   }

   public void rollback() throws SQLException {
      if (connection != null && !connection.isClosed() && autoCommit == false) {
         connection.rollback();
         autoCommit = true;
      }
   }

   private <T> T mapper(ResultSet set, Class<T> cla) {
      try {
         // 创建对象
         Constructor<T> declaredConstructor = cla.getDeclaredConstructor();
         declaredConstructor.setAccessible(true);
         T t = declaredConstructor.newInstance();

         // 解析结果集
         ResultSetMetaData metaData = set.getMetaData();
         int columnCount = metaData.getColumnCount();
         for (int i = 1; i <= columnCount; i++) {
            // 获取列名 getColumnLabel 和 getColumnName 的区别：前者获取别名，后者获取列名
            String columnName = metaData.getColumnLabel(i).toLowerCase();

            // 判断是否由多个单词组成
            if (columnName.contains("_")) {
               // 有多个单词组成，转换为驼峰命名
               columnName = toCamelCase(columnName);
            }

            try {
               // 获取属性描述器
               PropertyDescriptor propertyDescriptor = new PropertyDescriptor(columnName, cla);

               Method writeMethod = propertyDescriptor.getWriteMethod();
               writeMethod.setAccessible(true);

               // 设置值
               Object value = set.getObject(i);
               writeMethod.invoke(t, value);
            } catch (Exception e) {
               // 没有对应的属性
               System.err.println("没有对应的属性：" + columnName + "，跳过");
            }
         }
         return t;
      } catch (Exception e) {
         throw new RuntimeException(e);
      }
   }


   private String toCamelCase(String columnName) {
      // 判断是否由多个单词组成
      if (columnName.contains("_")) {
         // 有多个单词组成，转换为驼峰命名  is_marry
         String[] split = columnName.split("_");
         StringBuilder sb = new StringBuilder(split[0]);
         for (int i = 1; i < split.length; i++) {
            String s = split[i];
            sb.append(s.substring(0, 1).toUpperCase()).append(s.substring(1).toLowerCase());
         }
         return sb.toString();
      }
      return columnName;
   }

   public boolean update(String sql, Object... params) throws SQLException {
      getConnection();
      statement = connection.prepareStatement(sql);
      if (params != null) {
         for (int i = 0; i < params.length; i++) {
            statement.setObject(i + 1, params[i]);
         }
      }
      /*在JDBC中，execute()方法用于执行SQL语句，并返回一个boolean值。
      如果SQL语句是一个查询语句（如SELECT语句），则execute()方法返回true；
      如果SQL语句是一个更新语句（如INSERT、UPDATE、DELETE语句），则execute()方法返回false。*/
      boolean execute = statement.execute();
      return execute;
   }

   public boolean delete(String sql, Object... params) throws SQLException {
      getConnection();
      statement = connection.prepareStatement(sql);
      if (params != null) {
         for (int i = 0; i < params.length; i++) {
            statement.setObject(i + 1, params[i]);
         }
      }
      return statement.execute();
   }


   public <T> List<T> selectList(String sql, IResultMapper<T> resultMapper, Object... params) throws SQLException {
      // 获取连接
      getConnection();
      // 创建 prepareStatement
      statement = connection.prepareStatement(sql);
      // 设置值
      if (params != null) {
         for (int i = 0; i < params.length; i++) {
            statement.setObject(i + 1, params[i]);
         }
      }
      // 执行操作
      resultSet = statement.executeQuery();
      List<T> list = new ArrayList<>();
      // 解析结果
      while (resultSet.next()) {
         T t = resultMapper.mapper(resultSet);
         list.add(t);
      }
      return list;
   }

   public int insert(String sql, Object... params) throws SQLException {
      getConnection();
      statement = connection.prepareStatement(sql);
      if (params != null) {
         for (int i = 0; i < params.length; i++) {
            statement.setObject(i + 1, params[i]);
         }
      }
      return statement.executeUpdate();
   }

   public <T> T selectOne(String sql, Class<T> cla, Object... params) throws SQLException {
      // 获取连接
      getConnection();

      // 创建 prepareStatement
      statement = connection.prepareStatement(sql);
      System.out.println(sql);
      // 设置值
      if (params != null) {
         for (int i = 0; i < params.length; i++) {
            statement.setObject(i + 1, params[i]);
         }
      }

      // 执行操作
      resultSet = statement.executeQuery();

      // 解析结果
      if (resultSet.next()) {
         return mapper(resultSet, cla);
//            return resultMapper.mapper(rs);
      }
      return null;
   }
   //查询总数
   public int selectTotal(String sql,Object...params) throws SQLException {
      getConnection();
      statement = connection.prepareStatement(sql);
      if (params != null) {
         for (int i = 0; i < params.length; i++) {
            statement.setObject(i + 1, params[i]);
         }
      }
      resultSet = statement.executeQuery();
      if (resultSet.next()){
         return resultSet.getInt(1);
      }
      return 0;
   }

   public <T> List<T> selectList(String sql, Class<T> cla, Object... params) throws SQLException {
      // 获取连接
      getConnection();

      // 创建 prepareStatement
      statement = connection.prepareStatement(sql);
      // 设置值
      System.out.println(params.length+"=====");
      if (params != null) {
         for (int i = 0; i < params.length; i++) {
            statement.setObject(i + 1, params[i]);
         }
      }
      System.out.println(statement);
      // 执行操作
      resultSet = statement.executeQuery();

      List<T> list = new ArrayList<>();
      // 解析结果
      while (resultSet.next()) {
         T t = mapper(resultSet, cla);
         list.add(t);
      }

      return list;
   }

   /**
    * 释放资源
    */
   public void close() {
      if (resultSet != null) {
         try {
            resultSet.close();
         } catch (SQLException e) {
            throw new RuntimeException(e);
         }
         resultSet = null;
      }
      if (statement != null) {
         try {
            statement.close();
         } catch (SQLException e) {
            throw new RuntimeException(e);
         }
         statement = null;
      }
      if (connection != null) {
         try {
            connection.close();
         } catch (SQLException e) {
            throw new RuntimeException(e);
         }
         connection = null;
      }
   }

   /**
    * 结果映射
    */
   @FunctionalInterface
   interface IResultMapper<T> {
      T mapper(ResultSet set);
   }

}
